const Database=require("better-sqlite3");
const path=require("path");
const db=new Database(path.join(__dirname,"./mydata.sqlite"),{
    fileMustExist:true,
    verbose:console.log
});

const loginFun=function(user={}){
    let sql="select id,username,password from t_user where username=? and password=?";
    let stmt=db.prepare(sql);
    let row=stmt.get(user.username,user.password);
    return row;
};

const loadClassList=function(){
    let sql="select id,name from t_class";
    let stmt=db.prepare(sql);
    let row=stmt.all();
    return row;//数组
};

const loadStudentList=function(){
    let sql=`
        select s.id,s.name,s.link,s.img,s.cid,c.name  cname
        from t_student s,t_class c 
        where s.cid=c.id
    `;
    let stmt=db.prepare(sql);
    let row=stmt.all();
    return row;//数组
};

// 添加班级
const insertClass=function(className=""){
    let sql="insert into t_class (name) values (?)";
    let stmt=db.prepare(sql);
    let row=stmt.run(className);
    return row;
};

//根据班级id查询班级信息
const findClassById=function(id=""){
    let sql="select id,name from t_class where id=?";
    let stmt=db.prepare(sql);
    let row=stmt.get(id);
    return row;
};

//修改班级
const updateClass=function(classObj={}){
    let sql="update t_class set name=? where id=?";
    let stmt=db.prepare(sql);
    let row=stmt.run(classObj.className,classObj.id);
    return row;
};


//删除班级
const deleteClass=function(id=''){
    let sql="delete from t_class where id=?";
    let stmt=db.prepare(sql);
    let row=stmt.run(id);
    return row;
};



//添加学生
const insertStudent=function(stu={}){
    let sql="insert into t_student (name,link,img,cid) values (?,?,?,?) ";
    let stmt=db.prepare(sql);
    let row=stmt.run(stu.name,stu.link,stu.img,stu.cid);
    return row;
};

//根据id查询学生的信息
const findStuById=function(id=""){
    let sql="select id,name,link,img,cid from t_student where id=?";
    let stmt=db.prepare(sql);
    let row=stmt.get(id);
    return row;
};

const updateStudentById=function(stu={}){
    let sql="update t_student set name=?,link=?,img=?,cid=? where id=?";
    let stmt=db.prepare(sql);
    let row=stmt.run(stu.name,stu.link,stu.img,stu.cid,stu.id);
    return row;
};

const deleteStudent=function(id=''){
    let sql="delete from t_student where id=?";
    let stmt=db.prepare(sql);
    let row=stmt.run(id);
    return row;
};


const isSameName=function(user={}){
    let sql="select id,username,password from t_user where username=? ";
    let stmt=db.prepare(sql);
    let row=stmt.get(user.username);
    return row;
};

const insertUser=function(user={}){
    let sql="insert into t_user (username,password) values (?,?)";
    let stmt=db.prepare(sql);
    let row=stmt.run(user.username,user.password);
    return row;
};
//判断是否重名
module.exports={
    loginFun,
    loadClassList,
    loadStudentList,
    insertClass,
    findClassById,
    updateClass,
    deleteClass,
    insertStudent,
    findStuById,
    updateStudentById,
    deleteStudent,
    isSameName,
    insertUser
};